#!/usr/bin/env python
# -*- coding: utf-8 -*-
from apps.models.data_model import Data,DeviceDataInfo

class DataRepository:
    """
    @Author: kindey
    @Date: 2025/8/7
    @Description:
    """
    @staticmethod
    def get_all_data():
        return Data.object.using('source').all()

    @staticmethod
    def get_data_by_id(data_id):
        try:
            return Data.objects.using('source').get(id=data_id)
        except Data.DoesNotExist:
            return None

    @staticmethod
    def get_data_by_device_id(device_id):
        return Data.objects.using('source').filter(device_id=device_id)

    @staticmethod
    def get_data_with_device_info(device_id):
        """
        获取指定设备的数据，包含设备相关信息
        对应SQL查询: 根据device_id获取数据并关联DictSmallClass表
        """
        return Data.objects.using('source').select_related().extra(
            tables=['DictSmallClass'],
            where=['data.device_id = DictSmallClass.id AND data.device_id = %s'],
            params=[device_id]
        ).order_by('data_time')

    @staticmethod
    def get_device_data_with_joins(device_id, start_time=None, end_time=None):
        # """
        # 获取设备数据及其关联的区域和类型信息
        # 对应复杂SQL查询
        # """
        # where_conditions = [
        #     'data.device_id = b.id',
        #     'c.id = b.field1',
        #     'd.dict_code = b.field2',
        #     'b.id = %s'
        # ]
        # params = [device_id]
        #
        # # 添加时间范围条件
        # if start_time:
        #     where_conditions.append('data.data_time >= %s')
        #     params.append(start_time)
        #
        # if end_time:
        #     where_conditions.append('data.data_time <= %s')
        #     params.append(end_time)
        #
        # return Data.objects.using('source').extra(
        #     select={
        #         'data_time': 'data.data_time',
        #         'data_value': 'CASE WHEN data.data_value1 > data.data_value2 THEN data.data_value1 ELSE data.data_value2 END',
        #         'dev_type': 'b.class_name',
        #         'dev_name': 'b.dict_name',
        #         'dev_type2': 'd.dict_name',
        #         'reg_name': 'c.dict_name',
        #         'data_id': 'data.id',
        #         'dev_id': 'b.id',
        #         'reg_id': 'c.id'
        #     },
        #     tables=['DictSmallClass as b', 'DictSmallClass as c', 'DictSmallClass as d'],
        #     where=where_conditions,
        #     params=params,
        #     order_by=['data.data_time']
        # )
        """
        获取设备数据及其关联的区域和类型信息
        使用原生SQL查询避免别名问题
        """
        from django.db import connections

        # 构建基础SQL查询
        sql = """
              SELECT data.data_time as data_time,
                     CASE
                         WHEN data.data_value1 > data.data_value2 THEN data.data_value1
                         ELSE data.data_value2
                         END        as data_value,
                     b.class_name   as dev_type,
                     b.dict_name    as dev_name,
                     d.dict_name    as dev_type2,
                     c.dict_name    as reg_name,
                     data.id        as id,
                     b.id           as dev_id,
                     c.id           as reg_id
              FROM data
                   INNER JOIN DictSmallClass b ON data.device_id = b.id
                   INNER JOIN DictSmallClass c ON c.id = b.field1
                   INNER JOIN DictSmallClass d ON d.dict_code = b.field2
              WHERE b.id = %s
              """

        params = [device_id]

        # 添加时间过滤条件
        if start_time:
            sql += "     AND data.data_time >= %s"
            params.append(start_time)

        if end_time:
            sql += "     AND data.data_time <= %s"
            params.append(end_time)

        sql += "ORDER BY data.data_time"

        # 执行查询
        with connections['source'].cursor() as cursor:
            cursor.execute(sql, params)
            columns = [col[0] for col in cursor.description]
            rows = cursor.fetchall()
            result = []
            for row in rows:
                row_dict = dict(zip(columns, row))
                # 创建 DeviceDataInfo 对象
                device_data_info = DeviceDataInfo(
                    id=row_dict['id'],
                    data_value=row_dict['data_value'],
                    dev_type=row_dict['dev_type'],
                    dev_name=row_dict['dev_name'],
                    dev_type2=row_dict['dev_type2'],
                    reg_name=row_dict['reg_name'],
                    dev_id=row_dict['dev_id'],
                    reg_id=row_dict['reg_id'],
                    data_time=row_dict['data_time']
                )
                result.append(device_data_info)
        return result

    @staticmethod
    def create_data(data_dict):
        return Data.objects.using('source').create(**data_dict)

    @staticmethod
    def update_data(data_id, data_dict):
        try:
            data = Data.objects.using('source').get(id=data_id)
            for key, value in data_dict.items():
                setattr(data, key, value)
            data.save()
            return data
        except Data.DoesNotExist:
            return None

    @staticmethod
    def delete_data(data_id):
        try:
            data = Data.objects.using('source').get(id=data_id)
            data.delete()
            return True
        except Data.DoesNotExist:
            return False
